package com.bw.mapper;

import com.bw.domain.Sugar;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarMapper {
    @Select("SELECT c2.`name`,sum(i.final_total_amount) value FROM order_info i,order_detail d,sku_info k,base_category3 c3,base_category2 c2 WHERE d.order_id=i.id and d.sku_id=k.id AND k.category3_id=c3.id and c3.category2_id=c2.id GROUP BY c2.`name` ORDER By value")
    List<Sugar> getSugarList();

    @Select("SELECT CONCAT(SUBSTRING(u.birthday,3,1),'0后') `name`,sum(i.final_total_amount) value from order_info i,user_info u WHERE i.user_id=u.id GROUP BY CONCAT(SUBSTRING(u.birthday,3,1),'0后')")
    List<Sugar> getSugarList1();

    @Select("SELECT yys name,sum(final_total_amount) value from order_info WHERE yys is not NULL GROUP BY yys")
    List<Sugar> getSugarList3();

    @Select("\n" +
            "SELECT b.name name,if(u.gender='F','男','女') gender,sum(i.final_total_amount) value  FROM user_info u,order_info i,order_detail d,sku_info k,base_trademark b WHERE u.id=i.user_id and i.id=d.order_id and d.sku_id=k.id and k.tm_id=b.id GROUP BY b.name ,gender")
    List<Sugar> getSugarList4();
}
